#!/usr/bin/env bash
# db-assign_sequential_id -- Assigns a unique integer to every row for a table using PostgreSQL sequence generator
# > eval "$(db-parse "$url")"
# > db-assign_sequential_id TABLE COLUMN BEGIN_ID [INCREMENT] [ORDER_BY]
##
set -euo pipefail

echo "PGXL is not longer supported."
exit 1

[[ $# -gt 0 ]] || usage "$0" "Missing TABLE"
[[ $# -gt 1 ]] || usage "$0" "Missing COLUMN"
[[ $# -gt 2 ]] || usage "$0" "Missing BEGIN_ID"
Table=$1 Column=$2 BeginId=$3 Increment=${4:-1}

[[ $Increment -ne 0 ]] || usage "$0" "INCREMENT must be non-zero"

# Use a PostgreSQL-XL PL/pgSQL UDF to assign IDs fast
# See: http://www.postgresql.org/docs/9.2/static/sql-createlanguage.html
# See: http://www.postgresql.org/docs/9.2/static/plpgsql.html
db-supports_pg_lang "plpgsql" &&
db-execute "
    CREATE OR REPLACE FUNCTION copy_table_assign_ids_replace(
      schema_name character varying,
      table_name character varying,
      col_name character varying,
      start_id bigint,
      increment bigint,
      order_by character varying DEFAULT '')
    RETURNS TEXT AS
    \$\$
    DECLARE
      table_cur text := quote_ident(schema_name) || '.' || quote_ident(table_name);
      table_old text := quote_ident(schema_name) || '.' || quote_ident(table_name || '__old');
      table_new text := quote_ident(schema_name) || '.' || quote_ident(table_name || '__new');
      cols text[] := ARRAY(SELECT
                        CASE lower(attname)
                        WHEN lower(col_name) THEN start_id || ' + ((row_number() over (' || order_by || ')) - 1) * ' || increment
                        ELSE quote_ident(attname)
                        END
                      FROM   pg_attribute
                      WHERE  attrelid = table_cur::regclass
                      AND    attnum > 0
                      AND    NOT attisdropped
                      ORDER  BY attnum);
    BEGIN
      RAISE NOTICE '%',  cols;
      EXECUTE 'drop table if exists ' || table_old || ' cascade;';
      EXECUTE 'drop table if exists ' || table_new || ' cascade;';
      EXECUTE 'create table ' || table_new || ' (like ' || table_cur || ' including all)';
      EXECUTE 'insert into ' || table_new || ' select ' || array_to_string(cols, ',') || ' from ' || table_cur;
      EXECUTE 'alter table ' || table_cur || ' rename to ' || table_name || '__old';
      EXECUTE 'alter table ' || table_new || ' rename to ' || table_name;
      RETURN '';
    END;
    \$\$ LANGUAGE 'plpgsql';

    SELECT copy_table_assign_ids_replace('public', '$Table', '$Column', $BeginId, $Increment);
"
